package com.css.fxfzypg.modules.surveytaskmanage.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.modules.maps.service.TaskRegionMapService;
import com.css.fxfzypg.modules.preassesstaskmanage.entity.PreAssessTaskEntity;
import com.css.fxfzypg.modules.preassesstaskmanage.vo.PreVo;
import com.css.fxfzypg.modules.surveytaskmanage.entity.FieldSurveyTaskEntity;
import com.css.fxfzypg.modules.surveytaskmanage.repository.FieldSurveyTaskRepository;
import com.css.fxfzypg.modules.surveytaskmanage.vo.FieldSurveyVo;
import com.css.fxfzypg.modules.surveytaskmanage.vo.TaskVo;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class FieldSurveyTaskRepositoryImpl implements FieldSurveyTaskRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Autowired
    SysAreaService sysAreaService;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Resource
    TaskRegionMapService taskRegionMapService;

    @Override
    public void saveTask(FieldSurveyTaskEntity fieldSurveyTaskEntity) {
        //处理空间数据，判断存在自相交则获取修复后的空间数据，否则返回原空间数据wkt
        String geomText = taskRegionMapService.getGeomValidWkt(fieldSurveyTaskEntity.getGeom());
        fieldSurveyTaskEntity.setGeom(geomText);

//        String sql = "insert into ypg_survey_task(id,task_year,task_num,task_name,task_area,task_region,task_manager,group_name,group_leader," +
//                "task_status,parent_task,investigation_report,del_flag,create_user,create_time,update_user,update_time,group_id,parent_id," +
//                "task_district,geom) " +
//                "values(:id, :taskYear, :taskNum, :taskName, :taskArea, :taskRegion, :taskManager, :groupName, :groupLeader, " +
//                ":taskStatus, :parentTask, :investigationReport, :delFlag, :createUser, :createTime, :updateUser, :updateTime, :groupId, :parentId, " +
//                ":taskDistrict, ST_GeometryFromText(:geom,4490))";
        String sql = "INSERT INTO ypg_survey_task(id, task_year, task_num, task_name, task_area, task_region, task_manager, group_name, group_leader, task_status, parent_task, investigation_report, del_flag, create_user, create_time, update_user, update_time, group_id, parent_id, task_district, intensity, extend1, extend2, extend3, extend4, extend5, geom, report_id, task_manager_id, group_leader_id) values(:id, :taskYear, :taskNum, :taskName, :taskArea, :taskRegion, :taskManager, :groupName, :groupLeader, :taskStatus, :parentTask, :investigationReport, :delFlag, :createUser, :createTime, :updateUser, :updateTime, :groupId, :parentId, :taskDistrict, :intensity, :extend1, :extend2, :extend3, :extend4, :extend5, ST_GeometryFromText(:geom, 4490), :reportId, :taskManagerId, :groupLeaderId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fieldSurveyTaskEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    /*@Override
    public Map<String,Object> getPage(HttpServletRequest request, TaskVo taskVo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())){
            whereSql.append(" and task_num = '"+taskVo.getTaskNum()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskName())){
            whereSql.append(" and task_name = '"+taskVo.getTaskName()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())){
            whereSql.append(" and task_status = '"+taskVo.getTaskStatus()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())){
            whereSql.append(" and (");
            if(!PlatformObjectUtils.isEmpty(taskVo.getProvince())){
                whereSql.append(" task_district like '%"+ taskVo.getProvince() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCity())){
                whereSql.append(" or task_district like '%"+ taskVo.getCity() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCounty())){
                whereSql.append(" or task_district like '%"+ taskVo.getCounty() +"%'");
            }
            whereSql.append(") ");
        }

        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        List<FieldSurveyTaskEntity> GroupEntities = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
        Integer total = jdbcTemplate.queryForObject("select count(*) from ypg_survey_task where del_flag = '0' ", Integer.class);
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",GroupEntities);
        return map;
    }*/
    @Override
    public Map<String, Object> findParentIdByTask(String parentId, TaskVo taskVo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        StringBuilder orderSql = new StringBuilder(" order by t.create_time desc");
        StringBuilder countSql = new StringBuilder("select count(*) from ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_COUN_GL_ROLE)) {
            //国家级管理员查询全部
            sql.append(" (select * from  ypg_survey_task where del_flag='0') t ");
        } else if (roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)) {//省级管理员
            //组员或专家
            sql.append("(select task.* from ypg_survey_task task,ypg_group_user u where task.group_id=u.group_id and u.user_id='").append(PlatformSessionContext.getUserID()).append("' and task.del_flag='0' union ");
            //任务管理员或组长
            sql.append("(select task.* from ypg_survey_task task where (task.task_manager_id='").append(PlatformSessionContext.getUserID()).append("' or task.group_leader_id='").append(PlatformSessionContext.getUserID()).append("') and task.del_flag='0') union ");
            //省级管理员只能看本省的
            String areaNameByAreaId = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            if (!PlatformObjectUtils.isEmpty(areaNameByAreaId)) {
                sql.append(" select * from ypg_survey_task where task_district like '%").append(areaNameByAreaId).append("%' and del_flag='0') t ");
            }
        } else {
            //如果是省级业务人员或国家级业务人员只能看到自己是组员、组长、专家或管理员的任务
            //组员或专家
            sql.append("(select task.* from ypg_survey_task task,ypg_group_user u where task.group_id=u.group_id and u.user_id='").append(PlatformSessionContext.getUserID()).append("' and task.del_flag='0' union ");
            //任务管理员或组长
            sql.append("(select task.* from ypg_survey_task task where (task.task_manager_id='").append(PlatformSessionContext.getUserID()).append("' or task.group_leader_id='").append(PlatformSessionContext.getUserID()).append("') and task.del_flag='0')) t ");
            //whereSql.append(" and create_user = '" + taskVo.getCreateUser() + "'");
        }

        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(parentId)) {
            whereSql.append(" and t.parent_id = '").append(parentId).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())) {
            whereSql.append(" and t.task_num like '%").append(taskVo.getTaskNum()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskName())) {
            whereSql.append(" and t.task_name like '%").append(taskVo.getTaskName()).append("%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())) {
            whereSql.append(" and t.task_status = '").append(taskVo.getTaskStatus()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getProvince())) {
            if (!PlatformObjectUtils.isEmpty(taskVo.getCity())) {
                if (!PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
                    if (("北京市".equals(taskVo.getProvince()) || "天津市".equals(taskVo.getProvince()) || "重庆市".equals(taskVo.getProvince()) || "上海市".equals(taskVo.getProvince()))) {
                        whereSql.append(" and t.task_district like '%").append(taskVo.getProvince()).append("-").append(taskVo.getCounty()).append("%'");
                    } else {
                        whereSql.append(" and t.task_district like '%").append(taskVo.getProvince()).append("-").append(taskVo.getCity()).append("-").append(taskVo.getCounty()).append("%'");
                    }
                } else {
                    whereSql.append(" and t.task_district like '%").append(taskVo.getProvince()).append("-").append(taskVo.getCity()).append("%'");
                }
            } else {
                whereSql.append(" and t.task_district like '%").append(taskVo.getProvince()).append("%'");
            }
        }

        StringBuilder sqlWhole = sql.append(whereSql).append(orderSql);

        countSql.append(" ( ").append(sql).append(") t");
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        List<FieldSurveyVo> list = jdbcTemplate.query(sqlWhole.append(limitSql).toString(), new BeanPropertyRowMapper<>(FieldSurveyVo.class));
        System.out.println(sqlWhole + "========" + countSql);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<FieldSurveyTaskEntity> getSubTask(String parentTaskId) {
        String sql = "select * from ypg_survey_task where parent_id = ? and del_flag = '0'";
        List<FieldSurveyTaskEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class), parentTaskId);
        return list;
    }

    @Override
    public FieldSurveyTaskEntity findById(String taskId) {
        String sql = "select * from ypg_survey_task where id = ? ";
        FieldSurveyTaskEntity fieldSurveyTaskEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class), taskId);
        return fieldSurveyTaskEntity;
    }

    @Override
    public void updateTask(FieldSurveyTaskEntity fieldSurveyTaskEntity) {
        String sql = "update ypg_survey_task set task_name = :taskName, group_id= :groupId ,group_leader = :groupLeader ,group_name = :groupName " +
                ",update_user = :updateUser ,update_time = :updateTime ,task_status = :taskStatus where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fieldSurveyTaskEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void completeTask(String taskStatus, String id) {
        String sql = "update ypg_survey_task set task_status = ? where id =?";
        jdbcTemplate.update(sql, taskStatus, id);
    }


    @Override
    public void assignGroup(FieldSurveyTaskEntity fieldSurveyTaskEntity) {
        String sql = "update ypg_survey_task set group_id= :groupId ,group_leader = :groupLeader,group_leader_id = :groupLeaderId ,group_name = :groupName ," +
                "update_user = :updateUser ,update_time = :updateTime ,task_status = :taskStatus,task_manager_id=:taskManagerId where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fieldSurveyTaskEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void deleteTask(String delFlag, String id) {
        String sql = "update ypg_survey_task set del_flag= ? where id = ?";
        jdbcTemplate.update(sql, delFlag, id);
    }

    @Override
    public void deleteFieldSurvey(String delFlag, String taskNum) {
        String sql = "update ypg_sampling_survey_new set del_flag= ? where task_code = ?";
        jdbcTemplate.update(sql, delFlag, taskNum);
    }

    @Override
    public Map<String, Object> getAppPage(HttpServletRequest request, TaskVo taskVo, int curPage, int pageSize, List<String> grouoIds) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        StringBuilder countSql = new StringBuilder(" select count(*) from ypg_survey_task");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        /*if(!PlatformObjectUtils.isEmpty(grouoIds)){
            whereSql.append(" or group_id in ('"+String.join("','", grouoIds)+"')");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getUserId())){
            whereSql.append(" or create_user = '"+taskVo.getUserId()+"'");
        }*/
        if (!PlatformObjectUtils.isEmpty(grouoIds) || !PlatformObjectUtils.isEmpty(taskVo.getUserId())) {
            whereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(taskVo.getUserId())) {
                whereSql.append(" create_user = '" + taskVo.getUserId() + "'");
            }
            if (!PlatformObjectUtils.isEmpty(grouoIds)) {
                whereSql.append(" or group_id in ('" + String.join("','", grouoIds) + "')");
            }
            whereSql.append(") ");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())) {
            whereSql.append(" and task_num = '" + taskVo.getTaskNum() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskName())) {
            whereSql.append(" and task_name = '" + taskVo.getTaskName() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())) {
            whereSql.append(" and task_status = '" + taskVo.getTaskStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
            whereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(taskVo.getProvince())) {
                whereSql.append(" task_district like '%" + taskVo.getProvince() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCity())) {
                whereSql.append(" or task_district like '%" + taskVo.getCity() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
                whereSql.append(" or task_district like '%" + taskVo.getCounty() + "%'");
            }
            whereSql.append(") ");
        }

        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<FieldSurveyTaskEntity> GroupEntities = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", GroupEntities);
        return map;
    }

    @Override
    public List<FieldSurveyVo> findSubIdByTask(String parentId, TaskVo taskVo) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and parent_id = '" + parentId + "'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (!roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) && !roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)) {
            whereSql.append(" and create_user = '" + taskVo.getCreateUser() + "'");
        } else if (roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE)) {
            String areaNameByAreaId = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            if (!PlatformObjectUtils.isEmpty(areaNameByAreaId)) {
                whereSql.append(" and task_district like '%" + areaNameByAreaId + "%'");
            }
        }

        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())) {
            whereSql.append(" and task_num like '%" + taskVo.getTaskNum() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskName())) {
            whereSql.append(" and task_name like '%" + taskVo.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())) {
            whereSql.append(" and task_status = '" + taskVo.getTaskStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
            whereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(taskVo.getProvince())) {
                whereSql.append(" task_district like '%" + taskVo.getProvince() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCity())) {
                whereSql.append(" or task_district like '%" + taskVo.getCity() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
                whereSql.append(" or task_district like '%" + taskVo.getCounty() + "%'");
            }
            whereSql.append(") ");
        }
        String sqlWhole = sql.append(whereSql).append(orderSql).toString();
        List<FieldSurveyVo> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyVo.class));
        return list;
    }

    @Override
    public String getGroupLeaderId(String taskNum) {
        try {
            String sql = "select group_leader_id from ypg_group where del_flag = '0' and id = (select group_id from ypg_survey_task  where del_flag = '0' and task_num ='" + taskNum + "\')";
            Map<String, Object> map = jdbcTemplate.queryForMap(sql);
            Object group_leader_id = map.get("group_leader_id");
            if (!PlatformObjectUtils.isEmpty(group_leader_id)) {
                return group_leader_id.toString();
            } else {
                return null;
            }
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> getTeamMembers(String taskNum) {
        try {
            String sql = "select user_id from ypg_group_user where group_id = (select group_id from ypg_group where id = (select group_id from ypg_survey_task  where del_flag = '0' and task_num = '" + taskNum + "\'))";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            return list;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public String getMaxParentNum(String taskYear, String divisionCode) {
        String sql = "select max(task_num) from ypg_survey_task where del_flag = '0' and parent_id = '0' and task_num like '%" + taskYear + "%'" +
                "and SUBSTRING (task_num, 6, 2)= '" + divisionCode + "'";
        String maxParentNum = jdbcTemplate.queryForObject(sql, String.class);
        return maxParentNum;
    }

    @Override
    public List<FieldSurveyTaskEntity> getAllParentTask(String parentTaskId, String userId) {
        StringBuilder sql = new StringBuilder("select * from ypg_survey_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");

        StringBuilder str = new StringBuilder("");
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (!roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) && !roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)) {
            whereSql.append(" and create_user = '" + userId + "'");
        } else if (roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE)) {
            String areaNameByAreaId = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            if (!PlatformObjectUtils.isEmpty(areaNameByAreaId)) {
                whereSql.append(" and task_district like '%" + areaNameByAreaId + "%'");
            }
        }


        if (!PlatformObjectUtils.isEmpty(parentTaskId)) {
            whereSql.append(" and parent_id = '" + parentTaskId + "'");
        }
        String sqlWhole = sql.append(whereSql).toString();

        List<FieldSurveyTaskEntity> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
        return list;
    }

    @Override
    public List<TaskVo> getTask() {
        String sql = "select task_num,task_name from ypg_survey_task where del_flag='0' and task_status != '2'";
        List<TaskVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVo.class));
        return list;
    }

    @Override
    public List<TaskVo> getTaskManagers() {
        String sql = "select task_num,create_user from ypg_survey_task where del_flag='0'";
        List<TaskVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVo.class));
        return list;
    }

    @Override
    public List<Map<String, Object>> getAllTaskNum() {
        String sql = "select task_num from ypg_survey_task where del_flag='0'";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public String getTaskNameByTaskNum(String taskNum) {
        try {
            String sql = "select task_name taskName from ypg_survey_task where del_flag='0' and task_num = ?";
            String taskName = jdbcTemplate.queryForObject(sql, String.class, taskNum);
            return taskName;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public Boolean updateTaskIntensity(String taskId, String intensity) {
        String sql = "update ypg_survey_task set intensity= ? where id = ?";
        return jdbcTemplate.update(sql, intensity, taskId) > 0;
    }

    @Override
    public FieldSurveyTaskEntity getParentTask(String parentTaskNum) {
        String sql = "select * from ypg_survey_task where del_flag = '0' and task_num = ? ";
        FieldSurveyTaskEntity fieldSurveyTaskEntity = new FieldSurveyTaskEntity();
        try {
            fieldSurveyTaskEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class), parentTaskNum);
            return fieldSurveyTaskEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public String getMaxSubTaskNum(String parentTaskNum) {
        String sql = "select max(task_num) from ypg_survey_task where del_flag = '0' and parent_task = ?";
        String maxSubTaskCNum = jdbcTemplate.queryForObject(sql, String.class, parentTaskNum);
        return maxSubTaskCNum;
    }

    @Override
    public void insertReportIdIntoTask(String id, String taskCode) {
        String sql = "update ypg_survey_task set report_id= ? where task_num = ?";
        jdbcTemplate.update(sql, id, taskCode);
    }

//    @Override
//    public List<PreVo> getFuTask() {
//        String sql = "select id,task_num,task_name,task_district from ypg_survey_task where del_flag='0' and task_status != '2' and parent_id ='0'";
//        List<PreVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PreVo.class));
//        return list;
//    }
    @Override
    public List<PreVo> getFuTask() {
        //String sql = "select id,task_num,task_name,task_district from ypg_survey_task where del_flag='0' and task_status != '2' and parent_id ='0'";
        StringBuilder sql = new StringBuilder("select * from ");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode")).append(",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_COUN_GL_ROLE)) {
            //国家级管理员查询全部
            sql.append(" (select id,task_num,task_name,task_district from  ypg_survey_task where del_flag='0') t ");
        } else if (roles.contains(FxfzConstants.YPG_PRI_GL_ROLE)) {//省级管理员
            //组员或专家
            sql.append("(select task.id,task.task_num,task.task_name,task.task_district from ypg_survey_task task,ypg_group_user u where task.group_id=u.group_id and u.user_id='").append(PlatformSessionContext.getUserID()).append("' and task.del_flag='0' union ");
            //任务管理员或组长
            sql.append("(select task.id,task.task_num,task.task_name,task.task_district from ypg_survey_task task where (task.task_manager_id='").append(PlatformSessionContext.getUserID()).append("' or task.group_leader_id='").append(PlatformSessionContext.getUserID()).append("') and task.del_flag='0') union ");
            //省级管理员只能看本省的
            String areaNameByAreaId = sysAreaService.getAreaNameByAreaId(sUser.getProvinceId());
            if (!PlatformObjectUtils.isEmpty(areaNameByAreaId)) {
                sql.append(" select id,task_num,task_name,task_district from ypg_survey_task where task_district like '%").append(areaNameByAreaId).append("%' and del_flag='0') t ");
            }
        } else {
            //如果是省级业务人员或国家级业务人员只能看到自己是组员、组长、专家或管理员的任务
            //组员或专家
            sql.append("(select task.id,task.task_num,task.task_name,task.task_district from ypg_survey_task task,ypg_group_user u where task.group_id=u.group_id and u.user_id='").append(PlatformSessionContext.getUserID()).append("' and task.del_flag='0' union ");
            //任务管理员或组长
            sql.append("(select task.id,task.task_num,task.task_name,task.task_district from ypg_survey_task task where (task.task_manager_id='").append(PlatformSessionContext.getUserID()).append("' or task.group_leader_id='").append(PlatformSessionContext.getUserID()).append("') and task.del_flag='0')) t ");
            //whereSql.append(" and create_user = '" + taskVo.getCreateUser() + "'");
        }
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PreVo.class));
    }

    @Override
    public String getExtent(String taskId) {
        String sql = "select ST_extent(geom) from ypg_survey_task where id='" + taskId + "'";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public String getcenter(String taskId) {
        String sql = "select ST_AsText(ST_Centroid(ST_extent(geom))) from ypg_survey_task where id='" + taskId + "'";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public List<TaskVo> getAllTask(SUser sUser) {
        String sql = "select task.task_num,task.task_name from ypg_survey_task task,ypg_group_user u where task.group_id=u.group_id and u.user_id='" + sUser.getUserId() + "' and task.del_flag='0' and task_status != '2'" +
                " union " +
                "(select task.task_num,task.task_name from ypg_survey_task task where (task.task_manager='" + sUser.getUserName() + "' or task.group_leader='" + sUser.getUserName() + "') and task.del_flag='0' and task_status != '2')";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskVo.class));
    }

    @Override
    public FieldSurveyTaskEntity getById(String xcdcTaskId) {
        try {
            return jdbcTemplate.queryForObject("select * from ypg_survey_task where id = '" + xcdcTaskId + "'", new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<FieldSurveyTaskEntity> getSubTaskByTaskCode(String taskCode) {
        String sql = "select * from ypg_survey_task where parent_task='"+taskCode+"' and del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));
    }

    @Override
    public List<FieldSurveyTaskEntity> getChildrenByParentId(String id) {
        String sql = "select * from ypg_survey_task where parent_id='"+id+"' and del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(FieldSurveyTaskEntity.class));

    }

    @Override
    public List<String> getGroupUserIdByTaskCode(String taskCode) {
        String sql="SELECT ygu.user_id FROM  ypg_group_user  ygu  LEFT JOIN ypg_survey_task yst ON ygu.group_id =yst.group_id where yst.task_num='"+taskCode+"' and  yst.del_flag ='0'  and ygu.role='0'";
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return list;
    }

    @Override
    public String getStructureFuture(String province, String city, String county, String buildingStructure, String taskCode) {
        StringBuilder sql = new StringBuilder();
        sql.append("select ");
        if ("0".equals(buildingStructure)) {
            sql.append("high_feature");
        } else if ("1".equals(buildingStructure)) {
            sql.append("steel_feature");
        } else if ("2".equals(buildingStructure)) {
            sql.append("concrete_feature");
        } else if ("3".equals(buildingStructure)) {
            sql.append("masonry_feature");
        } else if ("4".equals(buildingStructure)) {
            sql.append("brick_wood_feature");
        } else if ("5".equals(buildingStructure)) {
            sql.append("soil_feature");
        } else if ("6".equals(buildingStructure)) {
            sql.append("wood_feature");
        } else if ("7".equals(buildingStructure)) {
            sql.append("stone_feature");
        } else if ("8".equals(buildingStructure)) {
            sql.append("other_feature");
        }
        sql.append(" from ypg_sampling_survey_new where province='" + province + "' and city='" + city + "' and area='" + county + "' and task_code='" + taskCode + "'");
        List<String> strings = jdbcTemplate.queryForList(sql.toString(), String.class);
        return strings.size() > 0 ? strings.get(0) : "";
    }
}
